package com.huatai.vba.execute.utils;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.huatai.vba.common.utils.ToStringUtils;
import com.huatai.vba.file.dbf.DBFObject;
import com.huatai.vba.file.dbf.DBFUtils;
import com.huatai.vba.service.VbaExtendsService;
import com.linuxense.javadbf.DBFField;

@Service
public class DBFConvertDBServiceImpl implements DBFConvertDBService {

	private static final Logger logger = LoggerFactory.getLogger(DBFConvertDBServiceImpl.class);

	@Autowired
	private VbaExtendsService vbaExtendsService;

	@Override
	public void convertDBFToDB(String fileName) throws Exception {
		convertDBFToDB(fileName, null);
	}

	@Override
	public void convertDBFToDB(String fileName, String tableName) throws Exception {
		DBFObject object = DBFUtils.readDBF(fileName);
		Object[] data = object.getData();
		if (data == null || data.length == 0) {
			logger.info(fileName + "无数据，不用加载");
			return;
		}
		if (StringUtils.isBlank(tableName))
			tableName = fileName.substring(fileName.lastIndexOf("/") + 1, fileName.lastIndexOf("."));
		DBFField[] fields = object.getFields();
		String dropTableSql = "drop table " + tableName + " ";
		StringBuffer createTableSql = new StringBuffer("create table " + tableName);
		createTableSql.append("(");
		StringBuffer columns = new StringBuffer();
		for (int i = 0; i < fields.length; i++) {
			DBFField f = fields[i];
			String columnName = f.getName();
			createTableSql.append(columnName);
			columns.append(columnName);
			byte type = f.getDataType();
			if (DBFField.FIELD_TYPE_C == type || DBFField.FIELD_TYPE_M == type) {
				Integer len = f.getFieldLength();
				createTableSql.append(" ").append("VARCHAR2(" + len + ")");
			} else if (DBFField.FIELD_TYPE_D == type) {
				createTableSql.append(" ").append("DATE ");
			} else if (DBFField.FIELD_TYPE_L == type) {
				createTableSql.append(" ").append("Char(1)");
			} else if (DBFField.FIELD_TYPE_N == type || DBFField.FIELD_TYPE_F == type) {
				Integer len = f.getFieldLength();
				Integer deCount = f.getDecimalCount();
				createTableSql.append(" ").append("NUMBER(" + len + "," + deCount + ")");
			}
			if (i != fields.length - 1) {
				createTableSql.append(",");
				columns.append(",");
			}
		}
		createTableSql.append(") ");
		if (this.isTableExists(tableName))
			vbaExtendsService.executeSql(dropTableSql);

		vbaExtendsService.executeSql(createTableSql.toString());
		// 插入数值

		for (int i = 0; i < data.length; i++) {
			Object[] o = (Object[]) data[i];
			StringBuffer values = new StringBuffer();
			for (int j = 0; j < fields.length; j++) {
				Object value = o[j];
				if (value instanceof String) {
					values.append("'").append(value.toString().trim()).append("'");
				} else if (value instanceof Date) {
					values.append("to_date('").append(new SimpleDateFormat("yyyyMMddHHmmss").format((Date) value))
							.append("','yyyymmddhh24miss')");
				} else if (value instanceof Integer || value instanceof Long) {
					values.append(value);
				} else if (value instanceof Double) {
					BigDecimal tmp = new BigDecimal(((Double) value).doubleValue());
					values.append(ToStringUtils.decimalToString(tmp));
				} else if (value instanceof Float) {
					BigDecimal tmp = new BigDecimal(((Float) value).floatValue());
					values.append(ToStringUtils.decimalToString(tmp));
				} else if (value instanceof BigDecimal) {
					values.append(ToStringUtils.decimalToString((BigDecimal) value));
				} else {
					values.append("null");
				}
				if (j != fields.length - 1) {
					values.append(",");
				}
			}
			StringBuffer insertSql = new StringBuffer(
					"insert into " + tableName + "(" + columns.toString() + ")values(" + values.toString() + ")");
			vbaExtendsService.executeSql(insertSql.toString());
		}
	}

	private boolean isTableExists(String tableName) throws Exception {
		String sql = "select 1 from user_objects where object_name='" + (tableName.toUpperCase())
				+ "' and object_type='TABLE'";
		return vbaExtendsService.querySql(sql).size() == 1;
	}

	@Override
	public void convertDBToDBF(String fileName) throws Exception {
		convertDBToDBF(fileName, fileName.substring(fileName.lastIndexOf("/"), fileName.lastIndexOf(".")));
	}

	@Override
	public void convertDBToDBF(String fileName, String tableName) throws Exception {
		DBFObject object = new DBFObject();
		object.setFileName(fileName);
	}

}
